package org.arrow.db.dao;

import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.arrow.db.pojo.TableBean;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;


/**
 * Data access object (DAO) for domain model
 * @author MyEclipse Persistence Tools
 */
public class BaseHibernateDAO extends HibernateDaoSupport implements IBaseHibernateDAO {
	private static Log log = LogFactory.getLog(BaseHibernateDAO.class);
	private static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HHmmss");
	
//	public Session getSession() {
//		return HibernateSessionFactory.getSession();
//	}
	
	public void closeSession(){
		//HibernateSessionFactory.closeSession();
	}
	

    public Object creat(Object obj) throws DaoException {

        try {
            Session session = getSession();
            Transaction t=session.beginTransaction();
            session.save(obj);
            t.commit();
            releaseSession(session);
        }catch(RuntimeException e){
        	log.error(e);
        	throw new DaoException(e);
        } finally {
        	closeSession();
        }
        return obj;
    }

    public void delete(Object obj) throws DaoException {
        try {
            Session session = getSession();
            Transaction t=session.beginTransaction();
            session.delete(obj);
            t.commit();
            releaseSession(session);
        }catch(RuntimeException e){
        	log.error(e);
        	throw new DaoException(e);
        } finally {
        	closeSession();
        }

    }


    public void delete(Class c,String filedName,Object value) throws DaoException {
    	String hql="";
        try {
            Session session = getSession();
            Transaction t=session.beginTransaction();
            if(value.getClass().toString().indexOf("String")!=-1){
            	hql = "delete "+c.getName()+" a where a."+filedName+" = '"+value+"'";
            }else{
            	hql = "delete "+c.getName()+" a where a."+filedName+" = "+value;
            }
            session.createQuery(hql).executeUpdate();
            t.commit();
            releaseSession(session);
        }catch(RuntimeException e){
        	log.error(e);
        	throw new DaoException(e);
        } finally {
        	closeSession();
        }
    }
    
    public Object update(Object obj) throws DaoException {
        try {
            Session session = getSession();
            Transaction t=session.beginTransaction();
            session.update(obj);
            t.commit();
            releaseSession(session);
        }catch(RuntimeException e){
        	log.error(e);
        	throw new DaoException(e);
        } finally {
        	closeSession();
        }
        return obj;
    }
    
    
    public void save(Object obj) throws DaoException {
        try {
            Session session = getSession();
            Transaction t=session.beginTransaction();
            session.saveOrUpdate(obj);
            t.commit();
            releaseSession(session);
        }catch(RuntimeException e){
        	log.error(e);
        	throw new DaoException(e);
        } finally {
        	closeSession();
        }
    }

    public Object load(Class c, Object id) throws DaoException {
        Object object;
        try {
            Session session = getSession();
            object = session.get(c, (Serializable) id,LockMode.NONE );
            releaseSession(session);
        }catch(RuntimeException e){
        	log.error(e);
        	throw new DaoException(e);
        } finally {
        	closeSession();
        }
        return object;
    }
    
    private HibernateSqlBean getObjectCountDetail(HibernateSqlBean sql,String fatherName,Object object,boolean isDark) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException{
        Class c = object.getClass();
        Method method[] = c.getMethods();
        for (int i = 0; i < method.length; i++) {
            String name = method[i].getName();
            if(name.indexOf("getMin")==0){
                String fieldName = name.substring(6, 7).toLowerCase() + name.substring(7);
                if(fatherName!=null)
                	fieldName=fatherName+"."+fieldName;
                String returnType = method[i].getReturnType().toString();
                Object retObj = method[i].invoke(object, null);
                if (retObj != null) {
                    if (returnType.indexOf("String") != -1){
                        if(retObj.equals(""))
                            continue;
                        sql.setSql(sql.getSql()+" and a."+fieldName+" > :"+sql.addStringParam(retObj.toString()));
                    }else if(returnType.indexOf("Date") != -1){
                    	sql.setSql(sql.getSql()+" and a."+fieldName+" > to_date('"+dateFormat.format((Date)retObj)+"','yyyy-mm-dd hh24miss')");
                    }else
                    	sql.setSql(sql.getSql()+" and a."+fieldName+" > "+retObj);
                }
                continue;
            }
            
            if(name.indexOf("getMax")==0){
                String fieldName = name.substring(6, 7).toLowerCase() + name.substring(7);
                if(fatherName!=null)
                	fieldName=fatherName+"."+fieldName;
                String returnType = method[i].getReturnType().toString();
                Object retObj = method[i].invoke(object, null);
                if (retObj != null) {
                    if (returnType.indexOf("String") != -1){
                        if(retObj.equals(""))
                            continue;
                        sql.setSql(sql.getSql()+" and a."+fieldName+" < :"+sql.addStringParam(retObj.toString()));
                    }else if(returnType.indexOf("Date") != -1){
                    	sql.setSql(sql.getSql()+" and a."+fieldName+" < to_date('"+dateFormat.format((Date)retObj)+"','yyyy-mm-dd hh24miss')");
                    }else
                        sql.setSql(sql.getSql()+" and a."+fieldName+" < "+retObj);
                }
                continue;
            }
            
			if(name.indexOf("getNot")==0){
				String fieldName = name.substring(6, 7).toLowerCase() + name.substring(7);
                if(fatherName!=null)
                	fieldName=fatherName+"."+fieldName;
				String returnType = method[i].getReturnType().toString();
				Object retObj = method[i].invoke(object, null);
				if (retObj != null) {
					if (returnType.indexOf("String") != -1){
						if(retObj.equals(""))
							continue;
						sql.setSql(sql.getSql()+" and a."+fieldName+" <> :"+sql.addStringParam(retObj.toString()));
					}else
						sql.setSql(sql.getSql()+" and a."+fieldName+" <> "+retObj);
				}
				continue;
			}
			
			if(name.indexOf("getDwcin")==0){
				String fieldName = name.substring(8, 9).toLowerCase() + name.substring(9);
	            if(fatherName!=null)
	            	fieldName=fatherName+"."+fieldName;
				if(method[i].getReturnType().toString().indexOf("Set")==-1)
					continue;
				Object retObj = method[i].invoke(object, null);
				if (retObj != null){
					Object obj[]=((Set)retObj).toArray();
					if(obj.length==0){
						sql.setSql(sql.getSql()+" and a."+fieldName+" in ('')");
						continue;
					}
					String returnType=obj[0].getClass().getName();
					String sql1="";
					for(int j=0;j<obj.length;j++){
						if(returnType.indexOf("String")!=-1){
							sql1=sql1+"'"+obj[j]+"',";
						}else{
							sql1=sql1+obj[j]+",";
						}
					}
					sql.setSql(sql.getSql()+" and a."+fieldName+" in ("+sql1.substring(0,sql1.length()-1)+")");
				}
				continue;
			}
			if(name.indexOf("getDwcnull")==0){
				String fieldName = name.substring(10, 11).toLowerCase() + name.substring(11);
                if(fatherName!=null)
                	fieldName=fatherName+"."+fieldName;
				Object retObj = method[i].invoke(object, null);
				if (retObj != null) {
					sql.setSql(sql.getSql()+" and a."+fieldName+" is null ");
				}
				continue;
			}
			
            if (name.indexOf("get") != 0 || name.indexOf("getClass") == 0)
                continue;
            String fieldName = name.substring(3, 4).toLowerCase() + name.substring(4);
            if(fatherName!=null)
            	fieldName=fatherName+"."+fieldName;
            String returnType = method[i].getReturnType().toString();
            Object retObj = method[i].invoke(object, null);
            
            if (retObj != null) {//like 
            	if(returnType.indexOf("java.util.Set") != -1)
            		continue;
                if (returnType.indexOf("String") != -1){
                	if(isDark)
                	    sql.setSql(sql.getSql()+" and a."+fieldName+" like :"+sql.addStringParam("%"+retObj.toString()+"%"));
                    else
                    	sql.setSql(sql.getSql()+" and a."+fieldName+" = :"+sql.addStringParam(retObj.toString()));
                }else if(returnType.indexOf("object") != -1){
                	sql=getObjectCountDetail(sql,fieldName,retObj,isDark);
                }else
                	sql.setSql(sql.getSql()+" and a."+fieldName+" = "+retObj);
            }
        }
    	return sql;
    }
    

    public int getObjectCount(Object object,boolean isDark) throws DaoException {
        Class c = object.getClass();
        HibernateSqlBean sql=new HibernateSqlBean();
        int count=0;
        try {
        	sql=getObjectCountDetail(sql,null,object,isDark);
            if(sql.getSql().length()>0){
            	sql.setSql("select count(*) from "+c.getName()+" a where "+sql.getSql().substring(5));
            }else{
                sql.setSql("select count(*) from "+c.getName());
            }
            
            Session session = getSession();
            Query query=session.createQuery(sql.getSql());
            for(int i=0;i<sql.getStringParamList().size();i++){
            	LabelValueBean bean=(LabelValueBean)sql.getStringParamList().get(i);
            	query.setString(bean.getLabel(),bean.getValue());
            }
            count=( (Long)query.iterate().next() ).intValue();
            releaseSession(session);
        } catch (IllegalArgumentException e) {
            log.error(e);
            throw new  DaoException("IllegalArgumentException",e);
        } catch (IllegalAccessException e) {
            log.error(e);
            throw new  DaoException("IllegalAccessException",e);
        } catch (InvocationTargetException e) {
            log.error(e);
            throw new  DaoException("InvocationTargetException",e);
        }catch(RuntimeException e){
        	log.error(e);
        	throw new DaoException(e);
        } finally {
            closeSession();
        }
        
        return count;
    }
    

    public Object getMaxValue(Object object,String fieldName) throws DaoException {
    	Class c = object.getClass();
    	Object maxValue=null;
        try {
        	String hql="select max(a."+fieldName+") from "+c.getName()+" a ";    
            log.info(hql);
            Session session = getSession();
            maxValue= session.createQuery(hql).iterate().next();
            releaseSession(session);
        } catch (IllegalArgumentException e) {
            log.error(e);
            throw new  DaoException("IllegalArgumentException",e);
        } finally {
        	closeSession();
        }    	
        return maxValue;
    }
    
    public List<List<String>> getDataBySql(String sql) throws DaoException{
        Statement stmt=null;
        ResultSet rs = null;
        ResultSetMetaData rsmd=null;
        
        List<List<String>> data = new ArrayList<List<String>>();
        try {
            log.info(sql);
            Session session = getSession();
            stmt  =session.connection().createStatement();
            rs = stmt.executeQuery(sql);
            rsmd = rs.getMetaData();
			
			int columns = rsmd.getColumnCount();
	        
			while (rs.next()) {
				List<String> t = new ArrayList<String>();
				for (int i = 0; i < columns; i++){
					Object obj=rs.getObject(i+1);
					if(obj!=null)
						t.add(rs.getObject(i+1).toString());
					else
						t.add(null);
				}
				data.add(t);
			}
			
			rsmd=null;
			rs.close();
			stmt.close();
			session.connection().close();
            releaseSession(session);
        } catch (IllegalArgumentException e) {
            log.error(e);
            throw new  DaoException("IllegalArgumentException",e);
        } catch (HibernateException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
        	closeSession();
        }
        return data;
    }
    
    public TableBean sqlQuery(String sql) throws DaoException{
        Statement stmt=null;
        ResultSet rs = null;
        ResultSetMetaData rsmd=null;
        
        TableBean table = new TableBean();
        List<String> title = new ArrayList<String>();
        List<List<String>> data = new ArrayList<List<String>>();
        try {
            log.info(sql);
            Session session = getSession();
            stmt  =session.connection().createStatement();
            rs = stmt.executeQuery(sql);
            rsmd = rs.getMetaData();
			
			int columns = rsmd.getColumnCount();
	        for(int i=0;i<columns;i++){
	            title.add(rsmd.getColumnName(i+1));
	        }
	        table.setTitle(title);
	        
			while (rs.next()) {
				List<String> t = new ArrayList<String>();
				for (int i = 0; i < columns; i++){
					Object obj=rs.getObject(i+1);
					if(obj!=null)
						t.add(rs.getObject(i+1).toString());
					else
						t.add(null);
				}
				data.add(t);
			}
			table.setData(data);
			
			rsmd=null;
			rs.close();
			stmt.close();
			session.connection().close();
            releaseSession(session);
        } catch (IllegalArgumentException e) {
            log.error(e);
            throw new  DaoException("IllegalArgumentException",e);
        } catch (HibernateException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
        	closeSession();
        }
        return table;
    }
   
    
    private Criteria queryListDetail(Criteria criteria,String fatherName,Object object,boolean isDark) throws IllegalArgumentException, IllegalAccessException, DaoException,InvocationTargetException{
    	Class c = object.getClass();
    	Method method[] = c.getMethods();
        for (int i = 0; i < method.length; i++) {
            String name = method[i].getName();
            if(name.indexOf("getMin")==0){
                String fieldName = name.substring(6, 7).toLowerCase() + name.substring(7);
                if(fatherName!=null)
                	fieldName=fatherName+"."+fieldName;
                Object retObj = method[i].invoke(object, null);
                if (retObj != null && !retObj.equals("")) {
                	criteria.add(Restrictions.ge(fieldName, retObj));
                }
                    
                continue;
            }
            if(name.indexOf("getMax")==0){
                String fieldName = name.substring(6, 7).toLowerCase() + name.substring(7);
                if(fatherName!=null)
                	fieldName=fatherName+"."+fieldName;
                Object retObj = method[i].invoke(object, null);
                if (retObj != null && !retObj.equals("")){
                	criteria.add(Restrictions.le(fieldName, retObj));
                }
                continue;
            }
			if(name.indexOf("getNot")==0){
				String fieldName = name.substring(6, 7).toLowerCase() + name.substring(7);
	            if(fatherName!=null)
	            	fieldName=fatherName+"."+fieldName;
				Object retObj = method[i].invoke(object, null);
				if (retObj != null && !retObj.equals("")) 
					criteria.add(Restrictions.not(Restrictions.eq(fieldName, retObj)));
				continue;
			}
			if(name.indexOf("getDwcin")==0){
				String fieldName = name.substring(8, 9).toLowerCase() + name.substring(9);
	            if(fatherName!=null)
	            	fieldName=fatherName+"."+fieldName;
				Object retObj = method[i].invoke(object, null);
				Set set=(Set)retObj;
				if (retObj != null){
					if(set.size()>0){
						criteria.add(Restrictions.in(fieldName,set.toArray()));
					}else{
						throw new DaoException("getDwcin");
					}
				}
				continue;
			}
			if(name.indexOf("getDwcnull")==0){
				String fieldName = name.substring(10, 11).toLowerCase() + name.substring(11);
	            if(fatherName!=null)
	            	fieldName=fatherName+"."+fieldName;
				Object retObj = method[i].invoke(object, null);
				if (retObj != null)
					criteria.add(Restrictions.isNull(fieldName));
				continue;
			}
            if (name.indexOf("get") != 0 || name.indexOf("getClass") == 0)
                continue;
            String fieldName = name.substring(3, 4).toLowerCase() + name.substring(4);
            if(fatherName!=null)
            	fieldName=fatherName+"."+fieldName;
            String returnType = method[i].getReturnType().toString();
            Object retObj = method[i].invoke(object, null);
            if (retObj != null) {//like 
            	if(returnType.indexOf("java.util.Set") != -1)
            		continue;
                if (returnType.indexOf("String") != -1){
                    if(isDark)
                      criteria.add(Restrictions.like(fieldName, "%" +retObj + "%"));
                    else
					  criteria.add(Restrictions.eq(fieldName, retObj));
                }else if(returnType.indexOf("pojo") != -1){
                	if(returnType.endsWith("Id")) //id is not another table.
                		criteria=queryListDetail(criteria,fieldName,retObj,isDark);
                	else{
                		criteria.createAlias(fieldName, fieldName+"1");
                		criteria=queryListDetail(criteria,fieldName+"1",retObj,isDark);
                	}
                }else
                    criteria.add(Restrictions.eq(fieldName, retObj));
            }
        }
    	return criteria;
    }

    public List queryList(Object object, int firstResult, int maxResults,String orderField[][],boolean isDark) throws DaoException {
        List list=null;
        try {
            Session session = getSession();
            Criteria criteria = session.createCriteria(object.getClass());
            criteria=queryListDetail(criteria,null,object,isDark);
            if(orderField!=null && orderField.length>0){
            	for(int i=0;i<orderField.length;i++){
            		if(orderField[i][0].toUpperCase().equals("ASC")){
            			criteria.addOrder(Order.asc(orderField[i][1]));
            		}else{
            			criteria.addOrder(Order.desc(orderField[i][1]));
            		}
            	}
            }
            criteria.setFirstResult(firstResult);
            if(maxResults>0)
              criteria.setMaxResults(maxResults);
            list = criteria.list();
            releaseSession(session);
        } catch (DaoException e) {
        	list=new ArrayList();
        } catch (IllegalArgumentException e) {
            log.error(e);
            throw new  DaoException("IllegalArgumentException",e);
        } catch (IllegalAccessException e) {
            log.error(e);
            throw new  DaoException("IllegalAccessException",e);
        } catch (InvocationTargetException e) {
            log.error(e);
            throw new  DaoException("InvocationTargetException",e);
        } finally {
        	closeSession();
        }

        return list;
    }
    
    
}